package com.aizhixin.test.my.dao;

import java.sql.ResultSet;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import com.aizhixin.test.common.core.PublicErrorCode;
import com.aizhixin.test.common.exception.CommonException;
import com.aizhixin.test.my.dto.GeRenPaiMinDTO;
import com.aizhixin.test.my.dto.RankingTableDTO;
import com.aizhixin.test.question.dto.bestKonlege;

@Component
public class PaiHangBiaoDAO {
	@Autowired
	 private JdbcTemplate jdbcTemplate;
	/*
	 * 将所有的排行展示
	 */
	public List<RankingTableDTO> rankingTableSelect(Long a){
		long b=a.longValue();
		long c;
		long d;
		
		if(b==1) {
		    c=b-1;
		    d=c+10;
		}
		else {
			c=(b-1)*10;
			d=c+10;
		}
		
		List<RankingTableDTO> list;
		try {
			final String  sql="SELECT q.OPENID,q.USER_NAME,q.HEAD_SCULPTURE,q.CITY,p.NOW_POINTS FROM user_imformation as q, answer_points_president_accounts as p WHERE q.ID=p.USER_ID ORDER BY p.NOW_POINTS desc LIMIT ?,?";
			list = jdbcTemplate.query(sql, 
					new Object[] {c,d},
					(ResultSet rs, int rowNum) -> {
			        	RankingTableDTO test = new RankingTableDTO();
			        	test.setId(rs.getString("OPENID"));
			        	test.setCity(rs.getString("CITY"));
			        	test.setHeadPortrait(rs.getString("HEAD_SCULPTURE"));
			        	test.setName(rs.getString("USER_NAME"));
			        	test.setPoints(rs.getLong("NOW_POINTS"));
			        
			            return test;//返回存了ID代表的题的对象回去

			}
					);
			return list;
		} catch (DataAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new  CommonException(PublicErrorCode.QUERY_EXCEPTION.getIntValue(), "not data");
		}
	
			
			
	}
	/*
	 * 个人排名展示
	 */
	public GeRenPaiMinDTO selectGeRenPaiHang(Long UserId) {
		final String sql="SELECT  b.mac AS score_rank,b.user_id FROM(SELECT t.*,@mac :=@mac + 1 AS mac	FROM	(SELECT @mac := 0) r,	answer_points_president_accounts AS t	ORDER BY	t.NOW_POINTS DESC) 	AS b WHERE b.user_id = ?";
	List<GeRenPaiMinDTO> list=jdbcTemplate.query(sql,
			new Object[] {UserId},
			(ResultSet rs,int RowNum) ->{
				GeRenPaiMinDTO test=new GeRenPaiMinDTO();
				test.setUserId(rs.getLong("user_id"));
				test.setUserRank(rs.getLong("score_rank"));
				return test;
			});
	if(list==null||list.size()==0) {
		return null;
	}
	else {
		return list.get(0);
	}
	
	}
	
}
